Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# SORT command

The SORT command arranges the order of values in the current status list of a dimension or a dimension surrogate, or in a valueset.

Syntax

SORT dimension [byhierarchy] [bycriterion...]

where:

- byhierarchy is an optional phrase that uses a parent relation to arrange the order of values in the current status list of a hierarchical dimension or its dimension surrogate, or to assign values to a valueset, based on family relationships within the hierarchy. You can include only one byhierarchy phrase in a SORT statement. It must be the first phrase in a SORT statement.

  HIERARCHY parent-relation [INVERT] [DEPTH n] [SORTORPHANS]

- bycriterion uses an explicit criterion to arrange the order of values in the current status list of a dimension or its dimension surrogate, or to assign values to a valueset. You can include as many bycriterion phrases as you want in a SORT statement.

  {A|D} [NAFIRST] criterion

Arguments

dimension

  A text expression whose value is the name of a dimension, a dimension surrogate, or a valueset.

HIERARCHY

  Specifies that Oracle OLAP is to sort dimension values based on the values position in parentrel.

parent-relation

  Specifies the name of a child-parent self-relation for dimension. For each dimension value, the relation holds another value of the dimension which is its parent dimension value (the one immediately above it in a given hierarchy). This parent relation can have more than one dimension.

  Tip: You can specify a QDR of parent-relation to specify a single value.

INVERT

  Indicates that Oracle OLAP places the children in the hierarchy before their parents. (By default, children are placed after their parents.)

DEPTH n

  Specifies the number of generations down from the top of the hierarchy that Oracle OLAP should place into status and. there, for the values that are included in the result. The default value of n is 99. When you do not want any values in the result (that is, when you want a `NULL` status), specify `-1` for n. When you only want the top of the hierarchy in status (that is, those dimension values that do not have parents), specify `0` (zero).

SORTORPHANS

  Specifies that all first cousins whose parents are not in status are sorted together. By default, Oracle OLAP preserves the hierarchical structure when sorting children even when their parents are not in status.

A
D

  The order in which the values are to be sorted. `A` means ascending order (alphabetical when the sorting criterion is TEXT, ID, or a relation), and `D` means descending order (reverse alphabetical when the sorting criterion is TEXT, ID or a relation).

NAFIRST

  Specifies that `NA` values are to placed first in the sort rather than last.

criterion

  The expression to be used as a sorting criterion. Each criterion must be dimensioned by dimension. The first expression is the major sorting criterion. When the expression is multidimensional, SORT uses the first value in status for all dimensions other than the dimension being sorted. You cannot use a valueset as the sorting criterion.

Notes

Sorting a Dimension and a Valueset

When Oracle OLAP sorts a dimension, it sorts the temporary status list of a dimension, not the data dimensioned by it. Since many OLAP DML statements operate on data according to the current status of its dimensions, sorting a dimension appears to have the effect of sorting data. A dimension and any dimension surrogates for it share the same status. Therefore, a SORT statement on a dimension or any of its surrogates sorts them all.

When Oracle OLAP sorts a valueset, it sorts the actual values within the valueset. When you execute UPDATE and COMMIT commands after sorting a valueset, the values in the valueset are stored in that sorted order.

Sorting Alphabetically

To sort a TEXT or ID dimension or its valueset in alphabetical order, use the dimension itself as the sorting criterion.

    SORT district A district

Sort Order for Textual Data

The sort order for textual data in an alphabetical sort is controlled by the NLS_SORT option.

Sorting a Time Dimension

The values of dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR are stored internally as numbers. Therefore, when you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in ascending order, with the dimension itself as the sorting criterion, then the values in the status list or valueset are placed in chronological order. When you sort a dimensions of type DAY, WEEK, MONTH, QUARTER, and YEAR dimension or its valueset in descending order, then the values are placed in reverse chronological order.

Sorting Using a Relation as a Criterion

When you use a relation as your sorting criterion, then the sorting is done alphabetically; that is, the dimension or valueset is sorted according to an alphabetical list of the related dimension values. To use a relation as the sorting criterion and keep the related dimension values in their original order, you must use the following expression as your sorting criterion See Example 21-40, "Sorting Using a Relation as the Criterion".

    CONVERT(relation, INTEGER)

Sorting Conjoint Dimensions

You can sort a conjoint dimension or its valueset by criteria dimensioned by either the conjoint dimension itself or by one of its base dimensions.

Sorting Concat Dimensions

You can sort a concat dimension or its valueset by criteria dimensioned by either the concat dimension itself or by one of its component dimensions. See Example 21-41, "Sorting Based on a Concat" and Example 21-42, "Sorting Based on a Component".

Sorting a Worksheet

You cannot use a worksheet as a sort criterion. You must first use CONVERT to specify the data type to which values of the worksheet should be converted.

Examples

Example 21-40 Sorting Using a Relation as the Criterion

This example sorts districts according to their unit sales of tents for July 1996. They are sorted first by the region to which they belong and then in descending order of dollar sales. Notice that in the following SORT statement, a relation is used as the primary sorting criterion. This means that the districts are sorted by regions listed alphabetically.

    LIMIT month TO 'Jul96'
    LIMIT product TO 'Tents'
    SORT district A Region.District D sales

Assume you issue the following REPORT statement.

    REPORT DOWN district HEADING 'Region' region.district sales

The preceding statement produces the following report that reflects the work of the SORT statement.

    PRODUCT: Tents
                   --------MONTH--------
                   --------JUL96--------
    DISTRICT         Region     SALES
    -------------- ---------- ----------
    Dallas         Central    154,914.23
    Chicago        Central     79,934.42
    Atlanta        East       140,711.00
    Boston         East        93,972.49
    Seattle        West       123,700.17
    Denver         West       100,413.49

In the following SORT statement, CONVERT is used to keep the regions in their original order.

    SORT district A CONVERT(region.district INTEGER) D sales

Assume that you issue the following REPORT statement.

    REPORT DOWN district HEADING 'Region' region.district sales

The preceding statement produces the following report that reflects the work of the last SORT statement.

    PRODUCT: Tents
                   --------MONTH--------
                   --------JUL96--------
    DISTRICT         Region     SALES
    -------------- ---------- ----------
    Atlanta        East       140,711.00
    Boston         East        93,972.49
    Dallas         Central    154,914.23
    Chicago        Central     79,934.42
    Seattle        West       123,700.17
    Denver         West       100,413.49

When you want the dimension to keep the sorted order of its values permanently, use a MAINTAIN statement after you sort the dimension.

    SORT district A district
    MAINTAIN district MOVE VALUES(district) FIRST

Example 21-41 Sorting Based on a Concat

The following statements sort the concat dimension `reg.dist.ccdim` in ascending order based on all of its values and report the result.

    sort reg.dist.ccdim d reg.dist.ccdim
    report reg.dist.ccdim

The preceding statement produces the following results.

    REG.DIST.CCDIM
    --------------------
    <Region: West>
    <Region: East>
    <Region: Central>
    <District: Seattle>
    <District: Denver>
    <District: Dallas>
    <District: Chicago>
    <District: Boston>
    <District: Atlanta>

The following statements sort the concat dimension `reg.dist.ccdim` in ascending order based on all of its values and report the result.

    SORT reg.dist.ccdim A reg.dist.ccdim
    REPORT reg.dist.ccdim

The preceding statement produces the following results.

    REG.DIST.CCDIM
    --------------------
    <District: Atlanta>
    <District: Boston>
    <District: Chicago>
    <District: Dallas>
    <District: Denver>
    <District: Seattle>
    <Region: Central>
    <Region: East>
    <Region: West>

Example 21-42 Sorting Based on a Component

The following statements sort the concat dimension `reg.dist.ccdim` in ascending order based on the values of one of its base dimensions and in descending order based on the values of its other base dimension, and report the result.

    SORT reg.dist.ccdim A region D district
    REPORT reg.dist.ccdim

The preceding statement produces the following results.

    REG.DIST.CCDIM
    --------------------
    <REGION: CENTRAL>
    <REGION: EAST>
    <REGION: WEST>
    <DISTRICT: SEATTLE>
    <DISTRICT: DENVER>
    <DISTRICT: DALLAS>
    <DISTRICT: CHICAGO>
    <DISTRICT: BOSTON>
    <DISTRICT: ATLANTA>

Example 21-43 Sorting by Hierarchy

Assume that your analytic workspace has two dimensions (`geog` and `time`), one relation (`geogparent`) and one variable (`sales`) with the following definitions.

    DEFINE GEOG DIMENSION TEXT
    DEFINE TIME DIMENSION TEXT
    DEFINE GEOGPARENT RELATION GEOG <GEOG>
    DEFINE SALES VARIABLE INTEGER <TIME GEOG>

If you issue a REPORT statement for `sales` when all of the values of `geog` are in status, Oracle OLAP produces the following report. This report displays the values for `geog` in the order in which they were added to the analytic workspace.

    REPORT sales
                   --------SALES--------
                   --------TIME---------
    GEOG              2004       2005
    -------------- ---------- ----------
    USA                 1,300         NA
    Massachusetts       3,881         NA
    Florida             3,479         NA
    Boston              2,644         NA
    Orlando             4,398         NA
    Miami               3,294         NA
    Pembroke            4,268         NA
    California          1,899         NA
    Texas               2,115         NA
    Los Angeles         2,394         NA
    San Francisco       1,334         NA
    Dallas                839         NA
    Houston               997         NA

However, assume that you issue a SORT statement to sort the values of `geog` by (1) `geogparent` and (2) descending by `sales`. After this sort if you issue a REPORT statement for `sales`, Oracle OLAP produces the following report. This report displays the values for `geog` with the states in descending order by `sales`, but with the cities of each state under the appropriate state.

    SORT geog HIERARCHY geogparent D sales
    REPORT SALES

                   --------SALES--------
                   --------TIME---------
    GEOG              2004       2005
    -------------- ---------- ----------
    USA                 1,300         NA
    Massachusetts       3,881         NA
    Pembroke            4,268         NA
    Boston              2,644         NA
    Florida             3,479         NA
    Orlando             4,398         NA
    Miami               3,294         NA
    Texas               2,115         NA
    Houston               997         NA
    Dallas                839         NA
    California          1,899         NA
    Los Angeles         2,394         NA
    San Francisco       1,334         NA

Example 21-44 Sorting Orphans of a Hierarchy

Assume that you have the same objects described in Example 21-43, "Sorting by Hierarchy". Assume also that the states of Florida and Massachusetts are not in status.

When you include the SORTORPHANS keyword in your SORT statement, the cities in Massachusetts and Florida are sorted together.

    SORT geog HIERARCHY geogparent SORTORPHANS D sales
    REPORT sales
                   ------------------SALES------------------
                   ------------------TIME-------------------
    GEOG                   2004                 2005
    -------------- -------------------- --------------------
    USA                           1,300                   NA
    Orlando                       4,398                   NA
    Pembroke                      4,268                   NA
    Miami                         3,294                   NA
    Boston                        2,644                   NA
    Texas                         2,115                   NA
    Houston                         997                   NA
    Dallas                          839                   NA
    California                    1,899                   NA
    Los Angeles                   2,394                   NA
    San Francisco                 1,334                   NA

However, if you exclude the SORTORPHANS keyword, Massachusetts cities and Florida cities are sorted separately.

    LIMIT geog COMPLEMENT 'Florida' 'Massachusetts'
    SORT geog HIERARCHY geogparent D sales
    REPORT SALES

                  --------SALES--------
                   --------TIME---------
    GEOG              2004       2005
    -------------- ---------- ----------
    USA                 1,300         NA
    Pembroke            4,268         NA
    Boston              2,644         NA
    Orlando             4,398         NA
    Miami               3,294         NA
    Texas               2,115         NA
    Houston               997         NA
    Dallas                839         NA
    California          1,899         NA
    Los Angeles         2,394         NA
    San Francisco       1,334         NA